From 2e744b592ae0edc2ddb5980023ba10f26487d42f Mon Sep 17 00:00:00 2001
From: Stevan Earl <stevan.earl@asu.edu>
Date: Tue, 21 Jul 2026 16:41:07 -0700
Subject: [PATCH] fix: consolidate clean fixes for problems #65-#68, #72, and
 #74

---
 conversion/clean.sql | 159 +++++++++++++++++++++++++++++++++++--------
 1 file changed, 129 insertions(+), 30 deletions(-)

diff --git a/conversion/clean.sql b/conversion/clean.sql
index 982dd82..e402450 100644
--- a/conversion/clean.sql
+++ b/conversion/clean.sql
@@ -531,37 +531,136 @@ UPDATE follow_arrival
   WHERE follow_arrival.fa_data_source IS NULL ;
 
 
--- Problem #65
-UPDATE aggression_event
-  SET ae_bad_observation_flag = NULL
-  WHERE ae_bad_observation_flag = ' ';
-
-UPDATE aggression_event
-  SET ae_bad_observation_flag = 'X'
-  WHERE ae_bad_observation_flag = 'YES';
-
-
--- Problem #66
-UPDATE aggression_event
-  SET ae_decided_flag = NULL
-  WHERE ae_decided_flag = ' ';
-
-
--- Problem #67
-UPDATE aggression_event
-  SET ae_multiple_aggressor_flag = NULL
-  WHERE ae_multiple_aggressor_flag = ' ';
-
-UPDATE aggression_event
-  SET ae_multiple_aggressor_flag = 'X'
-  WHERE ae_multiple_aggressor_flag = 'x';
-
-
--- Problem #68
-UPDATE aggression_event
-  SET ae_multiple_recipient_flag = NULL
-  WHERE ae_multiple_recipient_flag = ' ';
+--
+-- Aggression
+--
 
+-- Problems #65 through #68, #72, and #74
+-- Normalize AGGRESSION_EVENT boolean-style flags to the clean schema's
+-- canonical encoding: the empty string is false and X is true.  Normalize
+-- recipient certainty to N or Y.
+UPDATE clean.aggression_event
+SET
+  ae_decided_flag =
+    CASE
+      WHEN ae_decided_flag IS NULL OR BTRIM(ae_decided_flag) = ''
+        OR BTRIM(ae_decided_flag) = '?'
+        THEN ''
+      WHEN UPPER(BTRIM(ae_decided_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_decided_flag)) LIKE 'Y%'
+        THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_multiple_aggressor_flag =
+    CASE
+      WHEN ae_multiple_aggressor_flag IS NULL
+        OR BTRIM(ae_multiple_aggressor_flag) = ''
+        OR BTRIM(ae_multiple_aggressor_flag) = '?'
+        THEN ''
+      WHEN UPPER(BTRIM(ae_multiple_aggressor_flag))
+        LIKE 'X%' OR UPPER(BTRIM(ae_multiple_aggressor_flag))
+        LIKE 'Y%' THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_multiple_recipient_flag =
+    CASE
+      WHEN ae_multiple_recipient_flag IS NULL
+        OR BTRIM(ae_multiple_recipient_flag) = ''
+        OR BTRIM(ae_multiple_recipient_flag) = '?' THEN ''
+      WHEN UPPER(BTRIM(ae_multiple_recipient_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_multiple_recipient_flag)) LIKE 'Y%'
+        THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_bad_observation_flag =
+    CASE
+      WHEN ae_bad_observation_flag IS NULL
+        OR BTRIM(ae_bad_observation_flag) = ''
+        OR BTRIM(ae_bad_observation_flag) = '?'
+        THEN ''
+      WHEN UPPER(BTRIM(ae_bad_observation_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_bad_observation_flag)) LIKE 'Y%'
+        THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_bristle_flag =
+    CASE
+      WHEN ae_bristle_flag IS NULL
+        OR BTRIM(ae_bristle_flag) = ''
+        OR BTRIM(ae_bristle_flag) = '?'
+        THEN ''
+      WHEN UPPER(BTRIM(ae_bristle_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_bristle_flag)) LIKE 'Y%'
+        THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_display_flag =
+    CASE
+      WHEN ae_display_flag IS NULL
+        OR BTRIM(ae_display_flag) = ''
+        OR BTRIM(ae_display_flag) = '?'
+        THEN ''
+      WHEN UPPER(BTRIM(ae_display_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_display_flag)) LIKE 'Y%'
+        THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_chase_flag =
+    CASE
+      WHEN ae_chase_flag IS NULL
+        OR BTRIM(ae_chase_flag) = ''
+        OR BTRIM(ae_chase_flag) = '?'
+        THEN ''
+      WHEN UPPER(BTRIM(ae_chase_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_chase_flag)) LIKE 'Y%'
+        THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_contact_flag =
+    CASE
+      WHEN ae_contact_flag IS NULL
+        OR BTRIM(ae_contact_flag) = ''
+        OR BTRIM(ae_contact_flag) = '?'
+        THEN ''
+      WHEN UPPER(BTRIM(ae_contact_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_contact_flag)) LIKE 'Y%'
+        THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_vocal_flag =
+    CASE
+      WHEN ae_vocal_flag IS NULL
+        OR BTRIM(ae_vocal_flag) = ''
+        OR BTRIM(ae_vocal_flag) = '?'
+        THEN ''
+      WHEN UPPER(BTRIM(ae_vocal_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_vocal_flag)) LIKE 'Y%'
+        THEN 'X'
+      ELSE 'Invalid'
+    END,
+
+  ae_recipient_certainty_flag =
+    CASE
+      WHEN ae_recipient_certainty_flag IS NULL
+        OR BTRIM(ae_recipient_certainty_flag) = ''
+        OR BTRIM(ae_recipient_certainty_flag) = '?'
+        THEN 'N'
+      WHEN UPPER(BTRIM(ae_recipient_certainty_flag)) IN ('N', 'NO')
+        THEN 'N'
+      WHEN UPPER(BTRIM(ae_recipient_certainty_flag)) LIKE 'X%'
+        OR UPPER(BTRIM(ae_recipient_certainty_flag)) LIKE 'Y%'
+        THEN 'Y'
+      ELSE 'Invalid'
+    END
+;
 
 -- Problem #101
 UPDATE aggression_event
-- 
2.34.1